package service;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.fasterxml.jackson.databind.ObjectMapper;

@WebServlet("/cs")
public class Regist_Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 查询 MySQL 数据库获取统计数据
        List<GenderStat> stats = getStatsFromMySQL();
        ObjectMapper mapper = new ObjectMapper();
        String json = mapper.writeValueAsString(stats);

        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        resp.getWriter().write(json);
//        out.flush();
//        out.close();
    }

    private List<GenderStat> getStatsFromMySQL(){
        List<GenderStat> stats = new ArrayList<>();
        String url = "jdbc:mysql://192.168.244.11:3306/stats?serverTimezone=Asia/Shanghai&useSSL=false";
        String user = "root";
        String password = "123456";
     //   String query = "SELECT gender, status, SUM(count) as total FROM regist GROUP BY gender, status";


        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT gender, status, SUM(count) as total FROM regist GROUP BY gender, status")) {

            while (rs.next()) {
                stats.add(new GenderStat(rs.getString("gender"), rs.getString("status"), rs.getInt("total")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return stats;

    }
    private static class GenderStat {
        private String gender;
        private String status;
        private int count;

        public GenderStat(String gender, String status, int count) {
            this.gender = gender;
            this.status = status;
            this.count = count;
        }

        public String getGender() { return gender; }
        public String getStatus() { return status; }
        public int getCount() { return count; }
    }
    }




